﻿using DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using DBUtility;

namespace DAL
{
    public class InventoryDAL
    {
        /// <summary>
        /// 获取盘点单列表
        /// </summary>
        public DataSet GetInventory()
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select ROW_NUMBER() OVER (ORDER BY FBrNo)Num,FBrNo, ");
            commandText.Append(" CONVERT(varchar(100), Fdate, 23)Fdate,b.NAME FBillerID ");
            commandText.Append(" from Inventory left join sys_master b on b.ID = FBillerID ");
            commandText.Append(" where FStatus =1 order by FBrNo ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 获取盘点单里的明细
        /// </summary>
        public DataSet GetInventoryDetail(string FBrNo)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append("   select e.FItemID,e.PkgsBarCode,a.PlaceBarCode,(d.FName + a.FName +'-'+  CAST(Floor AS nvarchar)) PlaceName,e.NowNum  ");
            commandText.Append(" from InventoryDetail e left join t_StockPlace a on a.FSPID = PosiID ");
            commandText.Append("  left join t_Stock d on StockID=d.FItemID ");
            commandText.Append(" where FBrNo =  '" + FBrNo + "'");
            commandText.Append(" and Fstatus = 1");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 盘点更新明细信息
        /// </summary>
        public bool UpdateInventoryDetail(string FItemID, string NowNum)//,string PlaceBarCode
        {
            bool lb_return = false;
            //string stockID = ""; string PosiID = "";

            //lb_return = GetStockPosi(PlaceBarCode, out stockID, out PosiID); 盘点不更新库位信息

            //if (lb_return)
            //{
                StringBuilder commandText = new StringBuilder();
                commandText.Append(" update InventoryDetail set ");
                //commandText.Append(" PkgsBarCode='" + PkgsBarCode + "'");
                //commandText.Append(" StockID='" + stockID + "'");
                //commandText.Append(" ,PosiID='" + PosiID + "'");
                commandText.Append(" NowNum='" + NowNum + "'");
                commandText.Append(" ,Fstatus=2 ");
                commandText.Append(" where FItemID='" + FItemID + "'");

                try
                {
                    DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                    int i = DbHelperSQL.ExecuteSql(commandText.ToString());
                    if (i > 0)
                    { lb_return = true; }
                    else { lb_return = false; }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            //}

            return lb_return;
        }
        //获取仓库  库位
        public bool GetStockPosi(string PlaceBarCode, out string stockID, out string PosiID)
        {
            bool lb_return = false;
            stockID = ""; PosiID = "";
            DataSet dt = new DataSet();
            StringBuilder sql = new StringBuilder();
            sql.Append(" select FParentID,FSPID from t_StockPlace where PlaceBarCode = '" + PlaceBarCode + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    stockID = dr[0].ToString().Trim();
                    PosiID = dr[1].ToString().Trim();
                }
            }

            return lb_return;
        }
        //获取层
        public bool GetFloor(string PlaceBarCode, out string Floor)
        {
            bool lb_return = false;
            Floor = "";

            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append(" select MAX(a.Floor) Floor");
            commandText.Append(" from stk_Package a , t_StockPlace b ");
            commandText.Append(" where a.PkgsStatus in ('0','1')");
            commandText.Append(" and a.PosiID = b.FSPID ");
            commandText.Append(" and b.PlaceBarCode = '" + PlaceBarCode + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    Floor = dr[0].ToString().Trim();
                }
            }

            return lb_return;
        }
        //扫码获取明细信息
        public bool GetDetailNum(string FBrNo, string PlaceBarCode, out string FItemID, out string NowNum)
        {
            bool lb_return = false;
            FItemID = ""; NowNum = "";

            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append(" select FItemID,NowNum ");
            commandText.Append(" from InventoryDetail ");
            commandText.Append(" where FBrNo = '" + FBrNo + "' ");
            commandText.Append(" and PkgsBarCode = '" + PlaceBarCode + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
               

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    FItemID = dr[0].ToString().Trim(); if (FItemID != null && FItemID != "") { lb_return = true; } else { return false; }
                    NowNum = dr[1].ToString().Trim(); if (NowNum != null && NowNum != "") { lb_return = true; } else { return false; }
                }
            }

            return lb_return;
        }
        //对盘盈的条码进行备注1121 qls
        public bool NoteCode(string FBrNo, string PkgsBarCode)
        {
            bool lb = false;
            bool lb1 = false;
            string nt = string.Empty;
            StringBuilder sql = new StringBuilder();
            DataSet ds = new DataSet();
            sql.Append("select note from Inventory where FBrNo ='" + FBrNo + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(sql.ToString());
                if (ds != null)
                {
                    string note ="";
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                         note = dr["note"].ToString().Trim();
                    }
                   
                    note = note + ","+PkgsBarCode;
                  lb1 =  UpdateCode(FBrNo,note);
                  if (lb1)
                  {
                      lb = true;
                  }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb;
        }

        //更新盘盈条码
        public bool UpdateCode(string FBrNo, string code)
        {

            bool lb = false;
            StringBuilder sql = new StringBuilder();
            int i = 0;
            sql.Append(" update Inventory set note ='" + code + "' where FBrNo ='" + FBrNo + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                i = DbHelperSQL.ExecuteSql(sql.ToString());
                if (i > 0)
                {
                    lb = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb;
        }
        /// <summary>
        /// 判断是否可以审核
        /// </summary>
        public bool IsCheckExamine(string fBrNo, out string txttitle)
        {
            bool lb_return = false;
            txttitle = "";

            //判断单据状态
            string ls_states = CheckStates(fBrNo);
            if (ls_states == "1")
            {
                //判断明细录入是否正确
                bool detail = CheckDetail(fBrNo);
                if (!detail) { txttitle = "盘点单明细数量不正确！"; } else { lb_return = true; }
            }
            else
            {
                txttitle = "盘点单已审核！";
            }

            return lb_return;
        }
        public string CheckStates(string fBrNo)
        {
            string ls_return = "";

            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select FStatus from Inventory ");
            commandText.Append(" where FBrNo= '" + fBrNo + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    ls_return = dr[0].ToString().Trim();
                }
            }

            return ls_return;
        }
        public bool CheckDetail(string fBrNo)
        {
            bool lb_return = true;
            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select FItemID,OldNum,NowNum from InventoryDetail ");
            commandText.Append(" where FBrNo= '" + fBrNo + "'");

            try
            {
                dt = DbHelperSQL.Query(commandText.ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    string OldNum = dr["OldNum"].ToString().Trim();
                    string NowNum = dr["NowNum"].ToString().Trim();
                    //数值不能为空不能小于零
                    if (OldNum == "" || OldNum == null)
                    {
                        return false;
                    }
                    else
                    {
                        if (int.Parse(OldNum) < 0) { return false; }
                    }
                    if (NowNum == "" || NowNum == null)
                    {
                        return false;
                    }
                    else
                    {
                        if (int.Parse(NowNum) < 0) { return false; }
                    }

                }
            }
            return lb_return;
        }
        /// <summary>
        /// 审核盘点单
        /// </summary>
        public bool ExamineCheck(string fBrNo, string CheckID)
        {
            bool lb_return = false;
            string NowDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            StringBuilder ct1 = new StringBuilder();
            ct1.Append(" update Inventory set FStatus =2,");
            ct1.Append(" FExamineID ='" + CheckID + "',");
            ct1.Append(" FExamineDate ='" + NowDate + "'");
            ct1.Append(" where FBrNo ='" + fBrNo + "'");

            List<string> sql = new List<string>();
            sql.Add(ct1.ToString());

            //审核同时生成盘盈盘亏单
            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select PkgsID,PkgsBarCode,ItemSku,StickLen,Stick,FQty,StockID, ");
            commandText.Append(" PosiID,Floor,PkgsStatus,PkgsIsRegular,OldNum,NowNum,FType ");
            commandText.Append(" from InventoryDetail ");
            commandText.Append(" where FBrNo= '" + fBrNo + "'");
            try
            {
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (dt != null)
            {
                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    StringBuilder ct2 = new StringBuilder();
                    string PkgsID = dr["PkgsID"].ToString().Trim(); if (PkgsID == null) { PkgsID = ""; }
                    string PkgsBarCode = dr["PkgsBarCode"].ToString().Trim(); if (PkgsBarCode == null) { PkgsBarCode = ""; }
                    string ItemSku = dr["ItemSku"].ToString().Trim();
                    string StickLen = dr["StickLen"].ToString().Trim();
                    string Stick = dr["Stick"].ToString().Trim();
                    string FQty = dr["FQty"].ToString().Trim();
                    string StockID = dr["StockID"].ToString().Trim();
                    string PosiID = dr["PosiID"].ToString().Trim();
                    string Floor = dr["Floor"].ToString().Trim();
                    string PkgsIsRegular = dr["PkgsIsRegular"].ToString().Trim();
                    int OldNum = int.Parse(dr["OldNum"].ToString().Trim());
                    int NowNum = int.Parse(dr["NowNum"].ToString().Trim());
                    int Num = Math.Abs(OldNum - NowNum);
                    string Ftype = "";

                    if (Num > 0) //有数值差才生成盘盈盘亏
                    {
                        if (NowNum > OldNum) { Ftype = "2"; } else { Ftype = "1"; }
                        ct2.Append(" insert into InventoryBill(FBrNo,PkgsID,Ftype,PkgsBarCode,ItemSku,StickLen,Stick,");
                        ct2.Append(" FQty,StockID,PosiID,Floor,PkgsIsRegular,OldNum,NowNum,Num,FBillerID,FDate)values( ");
                        ct2.Append("'" + fBrNo + "',"); ct2.Append("'" + PkgsID + "',");
                        ct2.Append("'" + Ftype + "',"); ct2.Append("'" + PkgsBarCode + "',");
                        ct2.Append("'" + ItemSku + "',"); ct2.Append("'" + StickLen + "',");

                        ct2.Append("'" + Stick + "',"); ct2.Append("'" + FQty + "',");
                        ct2.Append("'" + StockID + "',"); ct2.Append("'" + PosiID + "',");
                        ct2.Append("'" + Floor + "',"); ct2.Append("'" + PkgsIsRegular + "',");

                        ct2.Append("'" + OldNum + "',"); ct2.Append("'" + NowNum + "',");
                        ct2.Append("'" + Num + "',"); ct2.Append("'" + CheckID + "',");
                        ct2.Append("'" + NowDate + "')");

                        sql.Add(ct2.ToString());
                    }

                }
            }

            try
            {
                int i = 0;
                i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else
                { lb_return = false; }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb_return;
        }
        /// <summary>
        /// 完成盘点单
        /// </summary>
        public bool FinishCheck(string fBrNo, string CheckID)
        {
            bool lb_return = false;
            string NowDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            StringBuilder ct1 = new StringBuilder();
            ct1.Append(" update Inventory set FStatus =3,");
            ct1.Append(" FFinishID ='" + CheckID + "',");
            ct1.Append(" FFinishDate ='" + NowDate + "'");
            ct1.Append(" where FBrNo ='" + fBrNo + "'");

            List<string> sql = new List<string>();
            sql.Add(ct1.ToString());

            //审核同时生成盘盈盘亏单
            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select FItemID,FBrNo,PkgsID,Ftype,PkgsBarCode,ItemSku,StickLen,Stick, ");
            commandText.Append(" FQty,StockID,PosiID,Floor,PkgsIsRegular,OldNum,NowNum,Num,FBillerID,FDate ");
            commandText.Append(" from InventoryBill ");
            commandText.Append(" where FBrNo= '" + fBrNo + "'");
            commandText.Append(" order by Ftype ");
            try
            {
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (dt != null)
            {
                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    StringBuilder ct2 = new StringBuilder();

                    string FItemID = dr["FItemID"].ToString().Trim();
                    string Ftype = dr["Ftype"].ToString().Trim();
                    string PkgsID = dr["PkgsID"].ToString().Trim();
                    string PkgsBarCode = dr["PkgsBarCode"].ToString().Trim();
                    string ItemSku = dr["ItemSku"].ToString().Trim();
                    string StickLen = dr["StickLen"].ToString().Trim();
                    string Stick = dr["Stick"].ToString().Trim();
                    string FQty = dr["FQty"].ToString().Trim();
                    string StockID = dr["StockID"].ToString().Trim();
                    string PosiID = dr["PosiID"].ToString().Trim();
                    string Floor = dr["Floor"].ToString().Trim();
                    string PkgsIsRegular = dr["PkgsIsRegular"].ToString().Trim();
                    string OldNum = dr["OldNum"].ToString().Trim();
                    string NowNum = dr["NowNum"].ToString().Trim();
                    string Num = dr["Num"].ToString().Trim();
                    int num = int.Parse(Num);

                    if (Ftype == "1")
                    {
                        //盘亏
                        ct2.Append(" update stk_Package set PkgsStatus =4, GALBillNo= '" + FItemID + "'");
                        ct2.Append(" where PkgsID = '" + PkgsID + "'");
                    }
                    else
                    {
                        //盘盈 入库
                        ct2.Append(" insert stk_Package(PkgsBarCode,ItemSku,StickLen,Stick,FQty,StockID,PosiID,");
                        ct2.Append(" Floor,InStkTime,InStkMan,PkgsStatus,GALBillNo,PkgsIsRegular)values(");//ql0128
                        ct2.Append("'" + PkgsBarCode + "',"); ct2.Append("'" + ItemSku + "',");
                        ct2.Append("'" + StickLen + "',"); ct2.Append("'" + Stick + "',");
                        ct2.Append("'" + FQty + "',"); ct2.Append("'" + StockID + "',");

                        ct2.Append("'" + PosiID + "',"); ct2.Append("'" + Floor + "',");
                        ct2.Append("'" + NowDate + "',"); ct2.Append("'" + CheckID + "',");
                        ct2.Append("'0',"); ct2.Append("'" + FItemID + "',");
                        ct2.Append("'" + PkgsIsRegular + "')");
                    }

                    for (int i = 0; i < num; i++)
                    {
                        sql.Add(ct2.ToString());
                    }

                }
            }

            try
            {
                int i = 0;
                i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else
                { lb_return = false; }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb_return;
        }



    }
}
